package com.ht.aems.fesmodel;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * Created by IntelliJ IDEA.
 *
 * @author Dong Shufeng
 *         Date: 2011-11-9
 */
public class DisStatusSqlPl {
    public static final String DIS_STATUS_TABLE_NAME = "Status_Time_";
    private static final int STATE_UNDEFINED = -1;
    public static final int STATE_NO_SIGNAL = 999;
    private static final int MS_OF_ONE_MINUTE = 60 * 1000;
    private static final int MINUTE_OF_ONE_DAY = 24 * 60;
    private static final long MS_OF_ONE_DAY = 1L * MINUTE_OF_ONE_DAY * MS_OF_ONE_MINUTE;
    private static final int BREAK_MINUTES_ALLOWED = 12;
    public static SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");
    private SimpleDateFormat format1 = new SimpleDateFormat("yyyyMM");

    private Connection conn;

    public DisStatusSqlPl(Connection conn) {
        this.conn = conn;
    }

    public void createStatusItems(int nid, String devId, Map<Integer, Integer> disValueToStatus, long start, long end) throws SQLException {
        Map<Integer, List<long[]>> statusTimes = getStatusItems(nid, devId, disValueToStatus, start, end);
        for(Integer type : statusTimes.keySet())
            for(long[] times : statusTimes.get(type))
                //addItem(devId, disValueToStatus.get(type), times[0], times[1]);
                addItem(devId, type, times[0], times[1]);
    }

    public Map<Integer, List<long[]>> getStatusItems(int nid, String devId, Map<Integer, Integer> disValueToStatus, long start, long end) throws SQLException {
        Calendar cStart = Calendar.getInstance();
        Calendar cEnd = Calendar.getInstance();
        cStart.setTimeInMillis(start / 1000 * 1000);
        cEnd.setTimeInMillis(end / 1000 * 1000);
        cStart.set(cStart.get(Calendar.YEAR), cStart.get(Calendar.MONTH), cStart.get(Calendar.DAY_OF_MONTH), 0, 0, 0);
        cEnd.set(cEnd.get(Calendar.YEAR), cEnd.get(Calendar.MONTH), cEnd.get(Calendar.DAY_OF_MONTH), 0, 0, 0);
        if (cStart.getTimeInMillis() > cEnd.getTimeInMillis()) {
            System.err.println("Input Error!");
            return null;
        }

        Map<Integer, List<long[]>> statusTimes = new HashMap<Integer, List<long[]>>();
        for(Integer type : disValueToStatus.values())
            statusTimes.put(type, new ArrayList<long[]>());
        Statement s = conn.createStatement();
        String sql;
        long startTime = cStart.getTimeInMillis(), endTime, currentDate = cStart.getTimeInMillis();
        int state = STATE_UNDEFINED, minute, lastMinute = 0;

        while (currentDate <= cEnd.getTimeInMillis()) {
            sql = "select nminute,nvalue from discrete_" + format.format(currentDate) + " where nid = " + nid + " order by nminute";
            ResultSet result = s.executeQuery(sql);
            if (!result.next())
                switch (state) {
                    case STATE_UNDEFINED:
                        state = STATE_NO_SIGNAL;
                    case STATE_NO_SIGNAL:
                        break;
                    default:
                        endTime = currentDate;
                        //System.out.println(showInfo(startTime, endTime, state));
                        //addItem(devId, disValueToStatus.get(state), startTime, endTime);
                        statusTimes.get(disValueToStatus.get(state)).add(new long[]{startTime, endTime});
                        startTime = endTime;
                        state = STATE_NO_SIGNAL;
                }
            else {
                minute = result.getInt(1);
                if (minute > 0) {
                    if (!(state == STATE_NO_SIGNAL || state == STATE_UNDEFINED)) {
                        endTime = currentDate - (MINUTE_OF_ONE_DAY - lastMinute) * MS_OF_ONE_MINUTE;
                        //System.out.println(showInfo(startTime, endTime, state));
                        //addItem(devId, disValueToStatus.get(state), startTime, endTime);
                        statusTimes.get(disValueToStatus.get(state)).add(new long[]{startTime, endTime});
                        startTime = endTime;
                    }
                    endTime = currentDate + minute * MS_OF_ONE_MINUTE;
                    //System.out.println(showInfo(startTime, endTime, STATE_NO_SIGNAL));
                    //addItem(devId, disValueToStatus.get(STATE_NO_SIGNAL), startTime, endTime);
                    statusTimes.get(disValueToStatus.get(STATE_NO_SIGNAL)).add(new long[]{startTime, endTime});
                    startTime = endTime;
                    state = result.getInt(2);

                    lastMinute = minute;
                    if (!result.next()) {
                        currentDate += MS_OF_ONE_DAY;
                        continue;
                    }
                }
                do {
                    minute = result.getInt(1);
                    switch (state) {
                        case STATE_NO_SIGNAL:
                            endTime = currentDate + minute * MS_OF_ONE_MINUTE;
                            //System.out.println(showInfo(startTime, endTime, STATE_NO_SIGNAL));
                            //addItem(devId, disValueToStatus.get(STATE_NO_SIGNAL), startTime, endTime);
                            statusTimes.get(disValueToStatus.get(STATE_NO_SIGNAL)).add(new long[]{startTime, endTime});
                            startTime = endTime;
                        case STATE_UNDEFINED:
                            state = result.getInt(2);
                            break;
                        default:
                            if (minute - lastMinute > BREAK_MINUTES_ALLOWED) {
                                endTime = currentDate + lastMinute * MS_OF_ONE_MINUTE;
                                //System.out.println(showInfo(startTime, endTime, state));
                                //addItem(devId, disValueToStatus.get(state), startTime, endTime);
                                statusTimes.get(disValueToStatus.get(state)).add(new long[]{startTime, endTime});
                                startTime = endTime;
                                endTime = currentDate + minute * MS_OF_ONE_MINUTE;
                                //System.out.println(showInfo(startTime, endTime, STATE_NO_SIGNAL));
                                //addItem(devId, disValueToStatus.get(STATE_NO_SIGNAL), startTime, endTime);
                                statusTimes.get(disValueToStatus.get(STATE_NO_SIGNAL)).add(new long[]{startTime, endTime});
                                startTime = endTime;
                                state = result.getInt(2);
                            } else if (state != result.getInt(2)) {
                                endTime = currentDate + minute * MS_OF_ONE_MINUTE;
                                //System.out.println(showInfo(startTime, endTime, state));
                                //addItem(devId, disValueToStatus.get(state), startTime, endTime);
                                statusTimes.get(disValueToStatus.get(state)).add(new long[]{startTime, endTime});
                                startTime = endTime;
                                state = result.getInt(2);
                            }
                    }
                    lastMinute = minute;
                } while (result.next());
                if (minute < MINUTE_OF_ONE_DAY - BREAK_MINUTES_ALLOWED) {
                    endTime = currentDate + minute * MS_OF_ONE_MINUTE;
                    //System.out.println(showInfo(startTime, endTime, state));
                    //addItem(devId, disValueToStatus.get(state), startTime, endTime);
                    statusTimes.get(disValueToStatus.get(state)).add(new long[]{startTime, endTime});
                    state = STATE_NO_SIGNAL;
                    startTime = endTime;
                }
            }
            currentDate += MS_OF_ONE_DAY;
            result.close();
        }
        endTime = currentDate - MS_OF_ONE_MINUTE;
        //System.out.println(showInfo(startTime, endTime, state));
        //addItem(devId, disValueToStatus.get(state), startTime, endTime);
        statusTimes.get(disValueToStatus.get(state)).add(new long[]{startTime, endTime});
        s.close();
        return statusTimes;
    }

    public void addItem(String id, int type, long startTime, long endTime) throws SQLException {
        String sql = "insert into " + DIS_STATUS_TABLE_NAME + format1.format(new Date(startTime)) + " values ('" + id + "'," + type + ",?,?)";
        PreparedStatement s = conn.prepareStatement(sql);
        s.setTimestamp(1, new Timestamp(startTime));
        s.setTimestamp(2, new Timestamp(endTime));
        s.executeUpdate();
        s.close();
        conn.commit();
    }

    public void clearItem(String id, Integer type, long startTime, long endTime) throws SQLException {
        String filter = " where tstarttime < ? and tendtime > ?";
        if(id != null)
            filter += " and sresourceid = '" + id + "'";
        if(type != null)
            filter += " and nstatusid = " + type;
        else
            filter += " and nstatusid < " + 20;
        String sql = "select * from " + DIS_STATUS_TABLE_NAME + format1.format(new Date(startTime)) + filter;
        PreparedStatement s = conn.prepareStatement(sql);
        s.setTimestamp(1, new Timestamp(endTime));
        s.setTimestamp(2, new Timestamp(startTime));
//        s.executeUpdate();
        ResultSet result = s.executeQuery();
        while (result.next()) {
            long sTime = result.getTimestamp("tStartTime").getTime();
            long eTime = result.getTimestamp("tEndTime").getTime();
            if(sTime < startTime)
                addItem(result.getString("sResourceId"), result.getInt("nStatusId"), sTime, startTime);
            if(eTime > endTime)
                addItem(result.getString("sResourceId"), result.getInt("nStatusId"), endTime, eTime);
        }
        s.close();
        sql = "delete from " + DIS_STATUS_TABLE_NAME + format1.format(new Date(startTime)) + filter;
        s = conn.prepareStatement(sql);
        s.setTimestamp(1, new Timestamp(endTime));
        s.setTimestamp(2, new Timestamp(startTime));
        s.executeUpdate();
        s.close();
        boolean autoCommit = conn.getAutoCommit();
        if(autoCommit)
           conn.setAutoCommit(false);
        conn.commit();
        conn.setAutoCommit(autoCommit);
    }

    public Map<Integer, long[][]> queryItem(String id, long startTime, long endTime) throws SQLException {
        String sql = "select nStatusId, tStartTime, tEndTime from " + DIS_STATUS_TABLE_NAME + format1.format(new Date(startTime)) + " where sResourceId = '" + id + "' and tEndTime > ? and tStartTime < ? order by tStartTime";
        PreparedStatement s = conn.prepareStatement(sql);
        s.setTimestamp(1, new Timestamp(startTime));
        s.setTimestamp(2, new Timestamp(endTime));
        ResultSet result = s.executeQuery();
        HashMap<Integer, List<long[]>> map2 = new HashMap<Integer, List<long[]>>();
        while (result.next()) {
            long[] l = new long[]{result.getTimestamp("tStartTime").getTime(), result.getTimestamp("tEndTime").getTime()};
            if (l[0] < startTime) l[0] = startTime;
            if (l[1] > endTime) l[1] = endTime;
            int statusId = result.getInt("nStatusId");
            if (!map2.containsKey(statusId))
                map2.put(statusId, new ArrayList<long[]>());
            map2.get(statusId).add(l);
        }
        HashMap<Integer, long[][]> map = new HashMap<Integer, long[][]>();
        for (int statusId : map2.keySet()) {
            map.put(statusId, new long[map2.get(statusId).size()][]);
            for (int i = 0; i < map2.get(statusId).size(); i++)
                map.get(statusId)[i] = map2.get(statusId).get(i);
        }
        result.close();
        s.close();
        return map;
    }

    public long[][] queryItem(String id, int type, long startTime, long endTime) throws SQLException {
        String sql = "select tStartTime, tEndTime from " + DIS_STATUS_TABLE_NAME + format1.format(new Date(startTime)) + " where sResourceId = '" + id + "' and nStatusId = " + type + " and tEndTime > ? and tStartTime < ? order by tStartTime";
        PreparedStatement s = conn.prepareStatement(sql);
        s.setTimestamp(1, new Timestamp(startTime));
        s.setTimestamp(2, new Timestamp(endTime));
        ResultSet result = s.executeQuery();
        ArrayDeque<long[]> stateTime = new ArrayDeque<long[]>();
        while (result.next()) {
            long[] l = new long[]{result.getTimestamp("tStartTime").getTime(), result.getTimestamp("tEndTime").getTime()};
            if (l[0] < startTime) l[0] = startTime;
            if (l[1] > endTime) l[1] = endTime;
            stateTime.add(l);
        }
        long[][] time = new long[stateTime.size()][2];
        int j = 0;
        while (!stateTime.isEmpty())
            time[j++] = stateTime.remove();
        result.close();
        s.close();
        return time;
    }
}
